﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SQLite;

namespace mysix
{
    internal class ClassDB
    {
        private SQLiteConnection connection;
        string connectionString = "Data Source=mydatabase.db;Version=3;";

        public ClassDB()
        {
            // 在构造函数中初始化数据库连接
            connection = new SQLiteConnection(connectionString);
        }

        public DataTable GetClassData()
        {
            // 创建一个 DataTable 以获取 Class 表的数据
            DataTable classDataTable = new DataTable();

            try
            {
                connection.Open();

                string selectClassSQL = "SELECT SchoolId, ClassId, ClassName FROM Class";
                SQLiteCommand selectClassCmd = new SQLiteCommand(selectClassSQL, connection);

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectClassCmd))
                {
                    adapter.Fill(classDataTable);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return classDataTable;
        }

        public void AddClass(int schoolId, int classId, string className)
        {
            try
            {
                connection.Open();

                string insertClassSQL = "INSERT INTO Class (SchoolId, ClassId, ClassName) VALUES (@SchoolId, @ClassId, @ClassName)";
                SQLiteCommand insertClassCmd = new SQLiteCommand(insertClassSQL, connection);
                insertClassCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                insertClassCmd.Parameters.AddWithValue("@ClassId", classId);
                insertClassCmd.Parameters.AddWithValue("@ClassName", className);
                insertClassCmd.ExecuteNonQuery();

                Console.WriteLine("Class added successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void DeleteClass(int schoolId, int classId)
        {
            try
            {
                connection.Open();

                string deleteClassSQL = "DELETE FROM Class WHERE SchoolId = @SchoolId AND ClassId = @ClassId";
                SQLiteCommand deleteClassCmd = new SQLiteCommand(deleteClassSQL, connection);
                deleteClassCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                deleteClassCmd.Parameters.AddWithValue("@ClassId", classId);
                deleteClassCmd.ExecuteNonQuery();

                Console.WriteLine("Class deleted successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void UpdateClass(int schoolId, int classId, string className)
        {
            try
            {
                connection.Open();

                string updateClassSQL = "UPDATE Class SET ClassName = @ClassName WHERE SchoolId = @SchoolId AND ClassId = @ClassId";
                SQLiteCommand updateClassCmd = new SQLiteCommand(updateClassSQL, connection);
                updateClassCmd.Parameters.AddWithValue("@ClassName", className);
                updateClassCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                updateClassCmd.Parameters.AddWithValue("@ClassId", classId);
                updateClassCmd.ExecuteNonQuery();

                Console.WriteLine("Class updated successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        public DataTable Search1Class(int schoolId, int classId)
        {
            DataTable resultTable = new DataTable();

            try
            {
                connection.Open();

                string selectClassSQL = "SELECT SchoolId, ClassId, ClassName FROM Class WHERE SchoolId = @SchoolId AND ClassId = @ClassId";
                SQLiteCommand selectClassCmd = new SQLiteCommand(selectClassSQL, connection);
                selectClassCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                selectClassCmd.Parameters.AddWithValue("@ClassId", classId);

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectClassCmd))
                {
                    adapter.Fill(resultTable);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return resultTable;
        }

        public void SearchClass(int schoolId, int classId)
        {
            try
            {
                connection.Open();

                string selectClassSQL = "SELECT SchoolId, ClassId, ClassName FROM Class WHERE SchoolId = @SchoolId AND ClassId = @ClassId";
                SQLiteCommand selectClassCmd = new SQLiteCommand(selectClassSQL, connection);
                selectClassCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                selectClassCmd.Parameters.AddWithValue("@ClassId", classId);

                using (SQLiteDataReader reader = selectClassCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        int classIdValue = reader.GetInt32(1);
                        string name = reader.GetString(2);
                        Console.WriteLine($"SchoolId: {id}, ClassId: {classIdValue}, ClassName: {name}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }

}
